package Database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Model.Processes;

public class ProcessesDB {
	
	public static ArrayList <Processes> GetProcessNames()
	{
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT DISTINCT(ProcessName) FROM qmsdb.processes ORDER BY ProcessName";

		ArrayList<Processes> PNamearry = new ArrayList<Processes>();
		Processes proc = null;

		try
		{
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				proc = new Processes();
				proc.setname(rs.getString("ProcessName"));
				

				PNamearry.add(proc);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			proc = null;
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (SQLException e)
			{
			}
		}

		return PNamearry;
	}
	public static ArrayList <Processes> GetProcesslatest()
	{
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT DISTINCT(ProcessName) FROM qmsdb.processes ORDER BY idProc DESC";

		ArrayList<Processes> PNamearry = new ArrayList<Processes>();
		Processes proc = null;

		try
		{
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				proc = new Processes();
				proc.setname(rs.getString("ProcessName"));
				

				PNamearry.add(proc);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			proc = null;
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (SQLException e)
			{
			}
		}

		return PNamearry;
	}
	
	public static ArrayList <Processes> GetProcedureNames()
	{
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM qmsdb.processes ORDER BY ProcedureName";

		ArrayList<Processes> ProcName = new ArrayList<Processes>();
		Processes proc = null;

		try
		{
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				proc = new Processes();
				proc.setProcedureName(rs.getString("ProcedureName"));
				proc.setId(rs.getInt("idProc"));
				

				ProcName.add(proc);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			proc = null;
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (SQLException e)
			{
			}
		}

		return ProcName;
	}
	
	public static ArrayList <Processes> GetProcedureLatest()
	{
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM qmsdb.processes ORDER BY idProc DESC";

		ArrayList<Processes> ProcName = new ArrayList<Processes>();
		Processes proc = null;

		try
		{
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				proc = new Processes();
				proc.setProcedureName(rs.getString("ProcedureName"));
				proc.setId(rs.getInt("idProc"));
				

				ProcName.add(proc);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
			proc = null;
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (SQLException e)
			{
			}
		}

		return ProcName;
	}
	
	public static boolean checkdupProcess(String ProcessN)
	{
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM processes WHERE ProcessName='"+ProcessN+"'";
		boolean result = false;
		
		try
		{
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				result=true;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (SQLException e)
			{
			}
		}

		return result;
	}
	public static boolean checkdupCode(String Code)
	{
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM processes WHERE Code='"+Code+"'";
		boolean result = false;
		
		try
		{
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				result=true;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (SQLException e)
			{
			}
		}

		return result;
	}
	public static boolean checkdupProcedureN(String ProcedureN)
	{
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM processes WHERE ProcedureName='"+ProcedureN+"'";
		boolean result = false;
		
		try
		{
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				result=true;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			}
			catch (SQLException e)
			{
			}
		}

		return result;
	}
}
